Multiple Columns In Data Bound Drop Down List

Objective: This article describes how you can add two columns bound data to a DropDownList.

Background: Almost always when I use a data bound drop down list, I find it necessary to display data from more than one column of my database table. In classic ASP this used to be a no brainer but as you may have noticed in asp.net things are a little different which sometimes leads to quite a bit of hair pulling, eye poking and other types of chain yanking. So, to avoid injury, my quick solution for this is to concatenate or combine the data columns in my query string. Surely, there are other ways of doing this, but this is quick and requires no extra strenuous code writing and it does the job very well.

Procedure: Follow instructions below to accomplish this task. This article uses Visual Web Developer Express 2008 and SQL Server Express 2005 with the NorthWind database as an example.

Create a new web form in your site and name it anything you want.
In design view, drag and drop a DropDownList control onto your page from the standard tab of your toolbox.
Click on the smart tag if its not already open and click on Choose Data Source. (image above)
In the Data Source Configuration Wizard select New Data Source. (image above)
Select “Database” and click OK. (image above)
In the next window I choose the NorthWind connection string that I already added earlier to my site as shown in the image below. If you don’t have a connection string configured yet for your database, click the “New Connection” button to configure one.
On the next window, select the first option to specify a custom SQL statement as shown below and click Next.
In the next window click the Query Builder button and then add the Employees table to the query builder as shown below and close the add table window.
I Use the following SQL statement to combine the employees First and Last Name. I also selected the EmployeeID column to use as the postback value for our drop down list control.
SELECT EmployeeID, FirstName + ‘ ‘ + LastName AS EmployeeName FROM Employees
Now click Next, then click Finish to close that window.
In the remaining window configure the fields as show in the picture below.
Click OK and you are done. Load the page into your browser. You should see something like the image below.
That’s it! Enjoy.